package com.hb.trade.api.routine.controller.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.hb.core.pojo.Attachment;
import com.hb.core.pojo.Attachments;
import com.hb.core.pojo.project.evaluation.dto.EvaluationCommitteeDTO;
import com.hb.core.pojo.project.evaluation.dto.EvaluationCommitteeMembersDTO;
import com.hb.core.pojo.project.opening.dto.OpeningSetDTO;
import com.hb.core.pojo.project.opening.entity.OpeningBidInfo;
import com.hb.core.pojo.project.opening.entity.OpeningBidTender;
import com.hb.core.pojo.project.section.dto.BidSectionDTO;
import com.hb.trade.api.routine.util.ReviewItemExcelUtil;
import com.hb.trade.client.project.BidOpeningClient;
import com.hb.trade.client.project.BidOpeningHallClient;
import com.hb.trade.client.project.SectionClient;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.fzzn.core.file.entity.HbFileInfo;
import org.fzzn.core.file.entity.UploadEntity;
import org.fzzn.core.file.service.PersistenceService;
import org.fzzn.core.model.constant.CommonConstant;
import org.fzzn.core.model.msg.Message;
import org.fzzn.core.util.IdUtil;
import org.fzzn.core.web.util.SpringUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.Iterator;
import java.util.List;



/**
 * describe:
 *
 * @author zhongdl
 * @date 2022/05/17
 */
@Slf4j
public class ScheduleExcelService {


    public static void scheduleExcel(List<OpeningBidTender> data,Long openingInfoId){
        if (CollUtil.isEmpty(data)){
            return;
        }
        Long sectionId = data.get(0).getSectionId();
        PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
        SectionClient sectionClient = SpringUtil.getBean(SectionClient.class);
        BidOpeningHallClient openingHallClient = SpringUtil.getBean(BidOpeningHallClient.class);
        BidOpeningClient bidOpeningClient = SpringUtil.getBean(BidOpeningClient.class);
        BidSectionDTO bidSectionDTO = new BidSectionDTO();
        bidSectionDTO.setSectionId(sectionId);
        Message<BidSectionDTO> bidSectionProjectInfo = sectionClient.getBidSectionProjectInfo(bidSectionDTO);
        bidSectionDTO = bidSectionProjectInfo.getData();
        OpeningSetDTO openingSetDTO = new OpeningSetDTO();
        openingSetDTO.setSectionId(sectionId);
        Message<OpeningSetDTO> detail = bidOpeningClient.detail(openingSetDTO);
        openingSetDTO = detail.getData();
        //生成表头
        ByteArrayOutputStream bos = null;
        InputStream in = null;
        try (HSSFWorkbook workbook = new HSSFWorkbook();
        ) {
            String sheetName = "开标一览表";
            InputStream stream = SpringUtils.getLocalStream("template/pingbiaobaogaomoban.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(4);
            //设置列宽
            HSSFSheet sheet = workbook.createSheet(sheetName);
            for (int i = 0; i < 22; i++) {
                sheet.setColumnWidth(i, sheetAt.getColumnWidth(i));
            }

            //总宽度
            int endWidth = 22;
            int average = 11;
            //第一行
            HSSFRow row1 = sheet.createRow(0);
            row1.createCell(0).setCellValue(sheetName);

            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            // 这里是合并excel中日期的两行为一行
            row1.setHeight((short) (39.95 *20));
            ReviewItemExcelUtil.createCellRangeAddress(sheet,0, 0, 0, (endWidth - 1) );

            //第二行
            HSSFRow row2 = sheet.createRow(1);
            row2.createCell(0).setCellValue("招标项目名称:" + bidSectionDTO.getProjectName());


            ReviewItemExcelUtil.createCellRangeAddress(sheet,1, 1, 0,  average - 1 );
            row2.createCell(average).setCellValue("招标项目编号:" + bidSectionDTO.getProjectCode());
            ReviewItemExcelUtil.createCellRangeAddress(sheet,1, 1, average ,  (endWidth - 1) );
            //第三行
            HSSFRow row3 = sheet.createRow(2);
            row3.createCell(0).setCellValue("标段(包)名称:" + bidSectionDTO.getSectionName());
            ReviewItemExcelUtil.createCellRangeAddress(sheet,2, 2, 0,  average - 1  );
            row3.createCell(average).setCellValue("标段(包)编号:" + bidSectionDTO.getSectionCode());
            ReviewItemExcelUtil.createCellRangeAddress(sheet,2, 2, average,  (endWidth - 1));
            int firstRow = 3;

            //生成一个表格，设置表格名称
            String decryptContent = data.get(0).getDecryptContent();
            JSONObject jsonObject = JSONUtil.parseObj(decryptContent,true,true);
            Iterator<String> iterator = jsonObject.keySet().iterator();
            HSSFRow rowHead = sheet.createRow(firstRow);
            rowHead.createCell(0).setCellValue("序号");
            rowHead.createCell(1).setCellValue("投标人");
            int rowHeadCell = 2;
            int reviewItemThirdColumnWith = (endWidth - 2) / (jsonObject.keySet().size() - 1);

            while (iterator.hasNext()){
                String head = iterator.next();
                if ("企业名称".equals(head)){
                    continue;
                }

                rowHead.createCell(rowHeadCell).setCellValue(head);
                int endCellColum = rowHeadCell + reviewItemThirdColumnWith - 1;
                if (endCellColum > rowHeadCell){
                    ReviewItemExcelUtil.createCellRangeAddress(sheet,firstRow, firstRow, rowHeadCell, endCellColum );
                }
                rowHeadCell += (reviewItemThirdColumnWith);

            }
            firstRow++;

            int size = data.size();
            for (int i = 0; i < size; i++) {
                OpeningBidTender openingBidTender = data.get(i);
                rowHeadCell = 2;
                JSONObject decryptContentJSON = JSONUtil.parseObj(openingBidTender.getDecryptContent());
                HSSFRow row = sheet.createRow(firstRow);
                row.createCell(0).setCellValue(i + 1);
                row.createCell(1).setCellValue(decryptContentJSON.get("企业名称",String.class));
                iterator = jsonObject.keySet().iterator();
                while (iterator.hasNext()){
                    String head = iterator.next();
                    if ("企业名称".equals(head)){
                        continue;
                    }
                    row.createCell(rowHeadCell).setCellValue(decryptContentJSON.get(head,String.class));
                    int endCellColum = rowHeadCell + reviewItemThirdColumnWith - 1;
                    if (endCellColum > rowHeadCell){
                        ReviewItemExcelUtil.createCellRangeAddress(sheet,firstRow, firstRow, rowHeadCell, endCellColum );
                    }
                    rowHeadCell += (reviewItemThirdColumnWith);
                }
                firstRow++;

            }
            HSSFRow row = sheet.createRow(firstRow);
            row.createCell(0).setCellValue("开标人：" + openingSetDTO.getHostName());
            ReviewItemExcelUtil.createCellRangeAddress(sheet,firstRow, firstRow, 0, average - 1 );
            row.createCell(average).setCellValue(" 监标人：" + openingSetDTO.getSupervisorName());
            ReviewItemExcelUtil.createCellRangeAddress(sheet,firstRow, firstRow, average, endWidth - 1 );
            ReviewItemExcelUtil.setFrame(3,firstRow,22,sheet);
            firstRow++;

            HSSFRow lastRow = sheet.createRow(firstRow);
            lastRow.createCell(0).setCellValue("日期:" + DateUtil.format(new Date(),new SimpleDateFormat("yyyy-MM-dd")));
            ReviewItemExcelUtil.createCellRangeAddress(sheet,firstRow, firstRow, 0, endWidth - 1 );
            ReviewItemExcelUtil.setExpertBoard(workbook,sheetName,null);
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);


            File scheduleExcel  = FileUtil.writeBytes(brray, new File(CommonConstant.TEMP_PATH + "/" + "开标一览表" + ".xls"));
            UploadEntity uploadEntity = new UploadEntity(scheduleExcel);
            List<HbFileInfo> hbFileInfos = persistenceService.uploadServer(uploadEntity);
            OpeningBidInfo openingBidInfo = new OpeningBidInfo();
            openingBidInfo.setId(openingInfoId);
            Attachments attachments = new Attachments();
            Attachment attachment = JSONUtil.toBean(JSONUtil.parseObj(hbFileInfos.get(0)), Attachment.class);
            attachment.setId(IdUtil.getId());
            attachments.add(attachment);
            openingBidInfo.setBidOpeningSchedule(attachments);
            openingHallClient.updateBidOpeningSchedule(openingBidInfo);
            in.close();
            bos.close();
        } catch (IOException e) {
            e.printStackTrace();


        }finally {
            if (bos != null){
                try {
                    bos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (in != null){
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 生成评委会信息
     *
     * @author tian
     * @date 2022-6-18 15:57
     */
    public static Attachment expertExcel(EvaluationCommitteeDTO committeeDTO) throws IOException {
        Attachment attachment = new Attachment();
        InputStream in = null;
        ByteArrayOutputStream bos = null;
        try {
            PersistenceService persistenceService = SpringUtil.getBean(PersistenceService.class);
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
            // 专家信息
            List<EvaluationCommitteeMembersDTO> expertList = committeeDTO.getExpertList();

            InputStream stream = SpringUtils.getLocalStream("template/evaluationCommitteeMembers.xlsx");
            XSSFWorkbook templateWorkbook = new XSSFWorkbook(stream);
            XSSFSheet sheetAt = templateWorkbook.getSheetAt(0);
            //生成表头
            XSSFRow row = sheetAt.getRow(1);
            row.getCell(1).setCellValue(committeeDTO.getProjectName());
            row.getCell(6).setCellValue(committeeDTO.getProjectCode());

            row = sheetAt.getRow(2);
            row.getCell(1).setCellValue(committeeDTO.getBiddingOrganizeInfo().getName());
            row.getCell(6).setCellValue(committeeDTO.getAgencyOrganizeInfo().getName());

            row = sheetAt.getRow(3);
            row.getCell(1).setCellValue(committeeDTO.getBidWayName());
            // 开标时间
            if (null != committeeDTO.getOpenBidTime()) {
                row.getCell(6).setCellValue(committeeDTO.getOpenBidTime().format(formatter));
            }

            row = sheetAt.getRow(4);
            row.getCell(1).setCellValue(expertList.size() + "人");
            // 抽取时间
            row.getCell(6).setCellValue(committeeDTO.getCreateTime().format(formatter));


            // 专家信息从第7行开始创建
            int rowNum = 7;
            // 组建专家信息
            for (int i = 0; i < expertList.size(); i++) {
                EvaluationCommitteeMembersDTO evaluationCommitteeMembersDTO = expertList.get(i);
                sheetAt.copyRows(6, 6, rowNum + i, new CellCopyPolicy());

                row = sheetAt.getRow(rowNum + i);
                row.getCell(0).setCellValue(evaluationCommitteeMembersDTO.getMemberName());
                if (null == evaluationCommitteeMembersDTO.getSex()) {
                    row.getCell(1).setCellValue("/");
                } else {
                    row.getCell(1).setCellValue(evaluationCommitteeMembersDTO.getSex().equals(0) ? "男" : "女");
                }
                row.getCell(2).setCellValue(evaluationCommitteeMembersDTO.getMemberMobile());
                row.getCell(3).setCellValue(evaluationCommitteeMembersDTO.getMemberIdCard());
                row.getCell(6).setCellValue(evaluationCommitteeMembersDTO.getMajor());
                row.getCell(7).setCellValue(evaluationCommitteeMembersDTO.getMemberCompany());
                row.getCell(8).setCellValue(evaluationCommitteeMembersDTO.getPassword());
            }

            // 末尾行
            int lastRowNum = rowNum + expertList.size();
            row = sheetAt.createRow(lastRowNum);
            row.setHeight((short) (30 * 20));

            XSSFCellStyle cellStyle = row.createCell(0).getCellStyle();
            //设置水平对齐的样式为居中对齐;
            //cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            XSSFCell cell1 = row.createCell(0);
            cell1.setCellValue("组建人：" + committeeDTO.getCreateName());
            cell1.setCellStyle(cellStyle);

            XSSFCell cell2 = row.createCell(3);
            cell2.setCellValue("监督人员：");
            cell2.setCellStyle(cellStyle);

            // 合并单元格
            ReviewItemExcelUtil.createCellRangeAddress(sheetAt, lastRowNum, lastRowNum, 0, 2);
            ReviewItemExcelUtil.createCellRangeAddress(sheetAt, lastRowNum, lastRowNum, 3, 8);

            bos = new ByteArrayOutputStream();
            templateWorkbook.write(bos);
            byte[] brray = bos.toByteArray();
            in = new ByteArrayInputStream(brray);

            // 上传
            File scheduleExcel = FileUtil.writeBytes(brray, new File(CommonConstant.TEMP_PATH + "/" + committeeDTO.getProjectCode() + "/" + "评委专家表" + ".xlsx"));
            UploadEntity uploadEntity = new UploadEntity(scheduleExcel);
            List<HbFileInfo> hbFileInfos = persistenceService.uploadServer(uploadEntity);
            if (CollUtil.isNotEmpty(hbFileInfos)) {
                // 保存入库
                Attachments attachments = new Attachments();
                attachment = JSONUtil.toBean(JSONUtil.parseObj(hbFileInfos.get(0)), Attachment.class);
                attachment.setId(IdUtil.getId());
                attachments.add(attachment);

                return attachment;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != in) {
                in.close();
            }
            if (null != bos) {
                bos.close();
            }
        }
        return attachment;
    }

}
